package com.turing.post.organization.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.List;

import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.organization.util.Merchant;
import com.turing.post.organization.util.MerchantBranch;
import com.turing.post.organization.util.MerchantBranchDao;
import com.turing.post.organization.util.OrganTreeList;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Dec 20, 2012
 */
@Stateless(mappedName = "organization.impl.MerchantBranchDaoImpl")
@Remote
@Local
public class MerchantBranchDaoImpl implements MerchantBranchDao {
	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@SuppressWarnings("unchecked")
	@Override
	public MerchantBranch getMerchantBranchByMerchantBranchName(
			String merchantBranchName) {
		String jpql = "select * from MerchantBranch m where m.merchantbranchname =?1";
		Query query = _em.createNativeQuery(jpql, MerchantBranch.class);
		query.setParameter(1, merchantBranchName);
		List<MerchantBranch> list = query.getResultList();
		if (list == null || list.isEmpty() || list.size() < 1) {
			return null;
		}
		return list.get(0);
	}

	// 获取商户分支机构自动生成顺序号
	@Override
	public String getAutoMerchantBranchSeqNo(String mrchBranchNo,
			String regionNo) {
		if (PCPSUtil.isEmpty(mrchBranchNo)) {
			return "";
		}
		String sql = "";
		// 发卡机构下销售机构顺序号
		if (!PCPSUtil.isEmpty(regionNo)) {
			sql = "select nvl(max(substr(t.merchantBranchNo,20,2)),0)+1 from merchantbranch t where t.merchantno is not null and t.supermerchantbranchno is null and substr(t.merchantBranchNo,0,15)='"
					+ mrchBranchNo + "' and RegionNo='" + regionNo + "'";
			Query query = _em.createNativeQuery(sql);
			return String.format("%02d", Integer.parseInt(query
					.getSingleResult().toString()));
		} else {
			// 销售机构层级顺序号
			Integer length = mrchBranchNo.trim().length()+1;
			sql = " select nvl(max(substr(t.merchantBranchNo,"
					+ length
					+ ",2)),0)+1 from merchantbranch t where t.supermerchantbranchno='"
					+ mrchBranchNo + "'";
			Query query = _em.createNativeQuery(sql);
			return String.format("%02d", Integer.parseInt(query
					.getSingleResult().toString()));
		}
	}

	/**
	 * 根据商户号,机构号查询商户分支机构信息
	 * 
	 * @param merchantNo
	 *            商户号
	 * @param merchantBranchNo
	 *            内部或外部机构号
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public MerchantBranch getMerchantBranchByMerchantTrans(String merchantNo,
			String merchantBranchNo) {
		String jpql = "select * from MerchantBranch m where m.merchantNo = ?1 and (m.merchantbranchno =?2 or m.ExternalBranchNo = ?2)";
		Query query = _em.createNativeQuery(jpql, MerchantBranch.class);
		query.setParameter(1, merchantNo);
		query.setParameter(2, merchantBranchNo);
		List<MerchantBranch> list = query.getResultList();
		if (list == null || list.size() != 1) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public MerchantBranch getMerchantBranchByMerchantBranchNo(
			String merchantBranchNo) {
		return _em.find(MerchantBranch.class, merchantBranchNo);
	}

	@SuppressWarnings( { "unchecked" })
	@Override
	public List<MerchantBranch> getMerchantBranchList(PCPSWebInput webInput,
			String merchantBranchNo, String merchantBranchName,
			String regionNo, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "select * from MerchantBranch t where 1=1";
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
			sql = sql + " and MerchantBranchNo like '" + issueCompanyNo + "%'";
		}
		sql = sql
				+ " and MerchantBranchNo like ?1 and MerchantBranchName like ?2 and (?3 is null or RegionNo like ''||?3||'%') order by MerchantBranchNo";
		Query query = _em.createNativeQuery(sql, MerchantBranch.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);

		if (PCPSUtil.isEmpty(merchantBranchNo)) {
			merchantBranchNo = "";
		}
		query.setParameter(1, "%" + merchantBranchNo + "%");
		if (PCPSUtil.isEmpty(merchantBranchName)) {
			merchantBranchName = "";
		}
		query.setParameter(2, "%" + merchantBranchName + "%");
		query.setParameter(3, regionNo);

		return query.getResultList();
	}

	@Override
	public BigInteger getMerchantBranchTotal(PCPSWebInput webInput,
			String merchantBranchNo, String merchantBranchName, String regionNo) {
		String sql = "select nvl(count(*),0) from MerchantBranch t where 1=1";
		if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			String issueCompanyNo = PCPSUtil.getIssueCompanyNo(webInput);
			sql = sql + " and MerchantBranchNo like '" + issueCompanyNo + "%'";
		}
		sql = sql
				+ " and MerchantBranchNo like ?1 and MerchantBranchName like ?2 and (?3 is null or RegionNo like ''||?3||'%')";
		Query query = _em.createNativeQuery(sql);

		if (PCPSUtil.isEmpty(merchantBranchNo)) {
			merchantBranchNo = "";
		}
		query.setParameter(1, "%" + merchantBranchNo + "%");
		if (PCPSUtil.isEmpty(merchantBranchName)) {
			merchantBranchName = "";
		}
		query.setParameter(2, "%" + merchantBranchName + "%");
		query.setParameter(3, regionNo);

		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	@SuppressWarnings("unchecked")
	public List<OrganTreeList> getMerchantBranchListByMerchantNo(
			String merchantNo) {
		Query query = _em
				.createNativeQuery(
						"select merchantbranchno branchNo,merchantbranchname branchName,supermerchantbranchno superBranchNo from MerchantBranch where MerchantNo='"
								+ merchantNo + "' order by MerchantBranchNo",
						OrganTreeList.class);
		return query.getResultList();
	}

	@Override
	public void insertMerchantBranch(MerchantBranch merchantBranch)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, merchantBranch);
	}

	@Override
	public void updateMerchantBranch(MerchantBranch merchantBranch)
			throws PCPSApplicationException {
		GeneralDao.update(_em, merchantBranch);
	}

	@SuppressWarnings("unchecked")
	@Override
	public HashMap<String, String> getMerchantMapListByMerchantNo(String icNo) {
		HashMap<String, String> res = new HashMap<String, String>();
		String jpql = "select c.* from Merchant c where c.IssueCompanyNo = ?1 order by merchantno";
		Query query = _em.createNativeQuery(jpql, Merchant.class);
		query.setParameter(1, icNo);
		List<Merchant> mList = query.getResultList();
		for (Merchant m : mList) {
			String sql = "select MerchantBranchNo from MerchantBranch where MerchantNo = '"
					+ m.getMerchantNo() + "'";
			Query querys = _em.createNativeQuery(sql);
			List<Object> obj_list = querys.getResultList();
			if (obj_list != null && obj_list.size() > 0) {
				String mbNo = obj_list.get(0).toString();
				res.put(m.getMerchantNo(), mbNo);
			}
		}
		return res;
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public List<OrganTreeList> getMerchantBranchListByMerchantBranchNo(
	// String branchNo, Integer minLen) {
	// String jpql = "select ib.merchantbranchno branchNo,ib.merchantbranchname
	// branchName,nvl(ib.supermerchantbranchno,'0') superBranchNo,"
	// + "case when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=0 then 1 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=2 then 2 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=4 then 3 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=6 then 4 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=10 then 6 end as layer from MerchantBranch ib where
	// ib.merchantbranchno like '"
	// + branchNo + "%' order by ib.merchantbranchno";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(branchNo)) {
	// jpql = "select ib.merchantbranchno branchNo,ib.merchantbranchname
	// branchName,nvl(ib.supermerchantbranchno,'0') superBranchNo,"
	// + "case when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=0 then 1 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=2 then 2 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=4 then 3 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=6 then 4 when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=8 then 5 "
	// + "when (length(ib.merchantbranchno)-"
	// + minLen
	// + ")=10 then 6 end as layer from MerchantBranch ib order by
	// ib.merchantbranchno";
	// }
	// Query query = _em.createNativeQuery(jpql, OrganTreeList.class);
	// List<OrganTreeList> list = query.getResultList();
	// return list;
	// }
	//
	// @Override
	// public Integer getMerchantBranchMinMerchantBranchNo(String branchNo) {
	// String sql = "select nvl(min(length(ib.merchantbranchno)),0) minlength
	// from MerchantBranch ib where ib.merchantbranchno like '"
	// + branchNo + "%'";
	// // 超级管理员全部显示
	// if (Config.SUPER_SYSOPUER_BRANCHNO.equals(branchNo)) {
	// sql = "select nvl(min(length(ib.merchantbranchno)),0) minlength from
	// MerchantBranch ib";
	// }
	// Query query = _em.createNativeQuery(sql);
	// return ((BigDecimal) query.getSingleResult()).intValue();
	// }
	/**
	 * 根据商户号查询商户分支机构
	 * 
	 * @param merchantNo
	 *            商户号
	 * @return List 商户分支机构列表
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getMerchantBranchByMerchantNo(String merchantNo) {
		if (PCPSUtil.isEmpty(merchantNo))
			return null;
		String sql = "select distinct(merchantbranchno) ,merchantbranchname from MerchantBranch where merchantbranchno like '"
				+ merchantNo + "%' ";
		Query query = _em.createNativeQuery(sql);
		List<Object[]> obj_list = query.getResultList();
		return obj_list;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MerchantBranch> getMerchantBranchByCondition(String merchantNo,
			String merchantBranchNo, String merchantBranchName, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "SELECT T.* FROM MERCHANTBRANCH T WHERE T.MERCHANTNO = ?1 ";
		if (!PCPSUtil.isEmpty(merchantBranchNo)) {
			sql = sql + "AND T.MERCHANTBRANCHNO = '" + merchantBranchNo + "' ";
		}
		if (!PCPSUtil.isEmpty(merchantBranchName)) {
			sql = sql + "AND T.MERCHANTBRANCHNAME LIKE '%" + merchantBranchName
					+ "%' ";
		}
		sql = sql + "ORDER BY T.MERCHANTBRANCHNAME";
		Query query = _em.createNativeQuery(sql, MerchantBranch.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, merchantNo);

		return query.getResultList();
	}

	@Override
	public BigInteger getMerchantBranchByConditionTotal(String merchantNo,
			String merchantBranchNo, String merchantBranchName) {
		String sql = "SELECT NVL(COUNT(*),0) FROM MERCHANTBRANCH T WHERE T.MERCHANTNO = ?1 ";
		if (PCPSUtil.isEmpty(merchantNo)) {
			sql = sql + "AND T.MERCHANTBRANCHNO = '" + merchantNo + "' ";
		}
		if (PCPSUtil.isEmpty(merchantBranchName)) {
			sql = sql + "AND T.MERCHANTBRANCHNAME LIKE '%" + merchantBranchName
					+ "%' ";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, merchantNo);

		return new BigInteger(query.getSingleResult().toString());
	}

}
